<?php

namespace App\Model\Business;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class ScheduleStuden extends Model
{
    # 数据表
    protected $table = 'schedules_students';

    # PK
    protected $primaryKey = 'id';

    # fillable fields
    protected $fillable = [
        'scheduleid',
        'studentuid',
        'identity',
    ];

    /**
     * 建立学生，课程 关系
     */
    static public function addRelation($scheduleid, $studentuid)
    {
        $obj = new ScheduleStuden();
        $obj->scheduleid = $scheduleid;
        $obj->studentuid = $studentuid;
        $obj->save();
    }

    /**
     * 删除学生，课程关系
     * @param $scheduleid
     * @param $studentuid
     * @return mixed
     */
    static public function delRelation($scheduleid, $studentuid)
    {
        return  static::where('scheduleid', $scheduleid)
                      ->where('studentuid', $studentuid)
                      ->delete();
    }

    /**
     * 获取学生课表
     * @param $name
     * @param $mobile
     * @return mixed
     */
    static public function getScheduleStudents($name, $mobile , $request=null)
    {
        $query   =  DB::table('schedules_students as relation')
                        ->select('relation.studentuid','students.name as studentname', 'students.ClassInNo',
                                 'students.mobile','schedules.name as schedulename', 'schedules.remark',
                                'schedules.begintime', 'schedules.endtime'
                        )
                        ->join('students', function ($join) use ($name,$mobile) {
                            $join->on('students.studentuid', '=', 'relation.studentuid');
                            if($name){
                                $join->where('students.name', 'like', "%$name%");
                            }
                            if($mobile){
                                $join->orwhere('students.mobile', 'like', "%$mobile%");
                            }
                        })
                        ->join('schedules', 'relation.scheduleid', '=', 'schedules.scheduleid')
                        ->groupby('students.studentuid');

        # 需要／不需要 分页
        if($request->has('page')) {
            $limit = 30;
            if($request->has('perPage')){
                $limit = $request->get('perPage');
            }
            $results = $query->paginate($limit)->items();
        }else{
            $results = $query->get();
        }

        foreach($results as $k=>$val){
            $begin = date('H:i', strtotime($val->begintime));
            $end = date('H:i', strtotime($val->endtime));
            $val->period = "$begin - $end";
            $results[$k] = $val;
        }
        return $results;
    }

    /**
     * 获取某个学生课表列表
     * @param $studentid
     * @param $begintime
     * @param $endtime
     * @return mixed
     */
    static function getStudentSchedules($studentid, $begintime, $endtime)
    {
        $results   =  DB::table('schedules_students as relation')
            ->select('students.name','students.ClassInNo','schedules.name', 'schedules.coursetypename', 'schedules.begintime',
                     'schedules.endtime','teachers.lastname', 'teachers.teacheruid',
                    'eeosync.firstEnterTime','eeosync.lastExitTime','eeosync.studyTime')
            ->join('students', 'students.studentuid', '=', 'relation.studentuid')
            ->join('schedules',  function ($join) use ($begintime, $endtime) {
                $join->on('relation.scheduleid', '=', 'schedules.scheduleid');
                if($begintime){
                    $join->where('schedules.begintime', '>=', $begintime);
                }
                if($endtime){
                    $join->where('schedules.endtime', '<',  $endtime);
                }
            })
            ->join('teachers', 'teachers.teacheruid', '=', 'schedules.teacheruid')
            ->leftJoin('eeosync', function ($join){
                $join->on('eeosync.scheduleid', '=', 'schedules.scheduleid')
                     ->on('eeosync.userid', '=', 'students.ClassInNo');
            })
            ->where('relation.studentuid', $studentid)
            ->get();

        foreach($results as $k=>$item){
            $begin = date('H:i', strtotime($item->begintime));
            $end = date('H:i', strtotime($item->endtime));
            $item->period = "$begin - $end";
            $item->scheduletimes = Schedule::calculRealSchedules($item->begintime, $item->endtime);
            $results[$k] = $item;
        }
        return $results;
    }

    static function getStudentByScheduleID($scheduleid, $identity=0)
    {
        $query   =  DB::table('schedules_students as relation')
            ->select('students.ClassInNo', 'students.name', 'students.studentuid', 'relation.identity')
            ->join('students', 'students.studentuid', '=', 'relation.studentuid')
            ->where('relation.scheduleid', $scheduleid);
        if($identity){
            $query->where('identity', $identity);
        }
        $results = $query->get();
        return $results;
    }

}

